"""
审计日志操作 + 单条 GROUPING SETS 统计
"""
import json
from datetime import datetime
from typing import List, Tuple
from typing import Optional, Dict, Any

from app.schemas.system_schemas import AuditLogRequest, AuditLogResponse
from common.database import DatabaseService


async def create_audit_log(db: DatabaseService, user_code: Optional[str], action: str, target_type: str,
						   target_id: str, request_ip: str, request_uri: str, result: bool = True) -> int:
	sql = """
          INSERT INTO base_audit_log (user_code, action, target_type, target_id, request_ip, request_uri, result,
                                      gmt_create)
          VALUES (%s, %s, %s, %s, %s, %s, %s, NOW()) \
		  """
	return db.insert(sql, (user_code, action, target_type, target_id, request_ip, request_uri, result))


async def get_audit_log_list(db: DatabaseService, request: AuditLogRequest) -> Tuple[int, List[AuditLogResponse]]:
	where, params = ["1=1"], []
	if request.user_id:
		where.append("al.user_code = %s")
		params.append(request.user_id)
	if request.action:
		where.append("al.action = %s")
		params.append(request.action)
	if request.target_type:
		where.append("al.target_type = %s")
		params.append(request.target_type)
	if request.start_time:
		where.append("al.gmt_create >= %s")
		params.append(request.start_time)
	if request.end_time:
		where.append("al.gmt_create <= %s")
		params.append(request.end_time)
	if request.result is not None:
		where.append("al.result = %s")
		params.append(request.result)
	where_clause = " AND ".join(where)

	total = db.get_one(f"SELECT COUNT(*) FROM base_audit_log al WHERE {where_clause}", params)["COUNT(*)"] or 0

	offset = (request.page - 1) * request.size
	sql = f"""
        SELECT al.id, al.user_code AS user_id, al.action, al.target_type, al.target_id,
               al.request_ip, al.request_uri, al.result, al.gmt_create, u.username
        FROM base_audit_log al
        LEFT JOIN base_user u ON al.user_code = u.user_code
        WHERE {where_clause}
        ORDER BY al.gmt_create DESC
        LIMIT %s OFFSET %s
    """
	params.extend([request.size, offset])
	rows = db.get_all(sql, params)
	logs = [AuditLogResponse.model_validate(r) for r in rows]
	return total, logs


async def get_audit_log_by_id(db: DatabaseService, log_id: int) -> Optional[AuditLogResponse]:
	sql = """
          SELECT al.id,
                 al.user_code AS user_id,
                 al.action,
                 al.target_type,
                 al.target_id,
                 al.request_ip,
                 al.request_uri,
                 al.result,
                 al.gmt_create,
                 u.username
          FROM base_audit_log al
                   LEFT JOIN base_user u ON al.user_code = u.user_code
          WHERE al.id = %s \
		  """
	row = db.get_one(sql, (log_id,))
	if not row:
		return None
	return AuditLogResponse.model_validate(row)


async def get_operation_statistics(db: DatabaseService,
								   start_time: datetime,
								   end_time: datetime) -> dict:
	sql = """
          -- @formatter:off
          SELECT 'action' AS dim, action AS val, COUNT(*) AS count
          FROM base_audit_log
          WHERE gmt_create BETWEEN %s
            AND %s
          GROUP BY action

          UNION ALL

          SELECT 'user' AS dim, IFNULL(u.username, 'unknown') AS val, COUNT(*) AS count
          FROM base_audit_log al
              LEFT JOIN base_user u
          ON al.user_code = u.user_code
          WHERE al.gmt_create BETWEEN %s
            AND %s
          GROUP BY al.user_code, u.username

          UNION ALL

          SELECT 'date' AS dim, DATE (gmt_create) AS val, COUNT(*) AS count
          FROM base_audit_log
          WHERE gmt_create BETWEEN %s
            AND %s
          GROUP BY DATE (gmt_create)

          UNION ALL

          SELECT 'result' AS dim, CAST(result AS CHAR) AS val, COUNT(*) AS count
          FROM base_audit_log
          WHERE gmt_create BETWEEN %s
            AND %s
          GROUP BY result \
		  -- @formatter:off	  
		  """
	params = (
		start_time, end_time,
		start_time, end_time,
		start_time, end_time,
		start_time, end_time
	)
	rows = db.get_all(sql, params)

	action_stats, user_stats, daily_stats, result_stats = [], [], [], {}
	for r in rows:
		if r['dim'] == 'action':
			action_stats.append({'action': r['val'], 'count': r['count']})
		elif r['dim'] == 'user':
			user_stats.append({'username': r['val'], 'count': r['count']})
		elif r['dim'] == 'date':
			daily_stats.append({'date': str(r['val']), 'count': r['count']})
		elif r['dim'] == 'result':
			result_stats[str(bool(int(r['val'])))] = r['count']

	return {
		'action_stats': action_stats,
		'user_stats': user_stats,
		'daily_stats': daily_stats,
		'result_stats': result_stats
	}


async def create_audit_log_with_body(
		db: DatabaseService,
		user_code: Optional[str],
		action: str,
		target_type: str,
		target_id: str,
		request_ip: str,
		request_uri: str,
		request_body: Optional[Dict[str, Any]],
		result: bool = True,
) -> int:
	"""
	写入审计日志并返回主键 ID
	"""
	sql = """
          INSERT INTO base_audit_log
          (user_code, action, target_type, target_id,
           request_ip, request_uri, request_body, result, gmt_create)
          VALUES (%s, %s, %s, %s, %s, %s, %s, %s, NOW()) \
		  """
	return db.insert(sql, (
		user_code, action, target_type, target_id,
		request_ip, request_uri,
		json.dumps(request_body, ensure_ascii=False) if request_body else None,
		result
	))


async def mark_audit_failed(db: DatabaseService, audit_id: int) -> int:
	"""
	把审计记录标记为失败
	返回受影响行数
	"""
	return db.update(
		"UPDATE base_audit_log SET result = 0 WHERE id = %s",
		(audit_id,)
	)
